### Replication Package for "Why is Intermediating Houses so Difficult? Evidence from iBuyers"
### Buchak, Matvos, Piskorski, and Seru
###
###
### buchak@stanford.edu

### Analysis around iBuyer PnL


library(data.table)
library(ggplot2)
library(lfe)
library(Hmisc)
library(zoo)
library(stargazer)
library(scales)



source('0_helper_functions.r')

setDTthreads(20)




Table_A6_Panel_B <- function() {
  data <- loadData() 
  data[,zip3 := substr(zip5,1,3)]
  
  # Median transaction price at each market-quarter.
  prices <- data[saleamount > quantile(data$saleamount,.01,na.rm=T) & saleamount < quantile(data$saleamount,.99,na.rm=T),j=list(index.price = median(saleamount,na.rm=T)),by=c('zip3','qtr')]
  
  # Get completed transactions
  data <- data[order(pclidirisfrmtd,qtr)]
  data[,buy.price  := saleamount]
  data[,sale.price := shift(saleamount,1,type='lead'),by='pclidirisfrmtd']
  data[,sale.qtr   := shift(qtr ,1,type='lead'),by='pclidirisfrmtd']
  data[,sale.date  := shift(date,1,type='lead'),by='pclidirisfrmtd']
  
  
  completed <- data[!is.na(sale.price) & sale.date != date]
  completed[,T := as.integer(sale.date- date)/365]
  completed[,pnl := sale.price / buy.price - 1]
  completed <- completed[pnl != 0] # get rid  of zero pnl observations
  completed <- merge(completed,prices,by=c('zip3','qtr'))
  completed <- merge(completed,prices,by.x=c('zip3','sale.qtr'),by.y=c('zip3','qtr'),suffixes=c('.buy','.sale'))
  completed[,index.pnl := index.price.sale / index.price.buy - 1]
  completed[,bid.ask.pnl := pnl - index.pnl]
  completed[,pnl.ann         := (pnl + 1)^(1/T) - 1]
  completed[,index.pnl.ann   := (index.pnl + 1)^(1/T) - 1]
  completed[,bid.ask.pnl.ann := (bid.ask.pnl + 1)^(1/T) - 1]
  
  
  completed[,Type := 'Individual']
  completed[iBuyer.buyer == 1,Type := 'iBuyer']
  completed[iBuyer.buyer == 0 & corporateindicator == 'Y',Type := 'Other corporate']
  
  completed <- completed[year %in% 2013:2018]
  mean(completed[Type == 'iBuyer']$pnl < 0)
  
  # Apply some filters
  # ready <- completed[ abs(pnl / (sale.qtr - qtr + 1)) < 1]
  ready <- completed[ abs(pnl.ann) < 3 & abs(bid.ask.pnl.ann) < 3 & abs(index.pnl.ann) < 3]
  
  # PnL analysis with regressions/hedonics.
  toReg <- ready[Type != 'Other corporate' & pnl.ann < 1 & pnl.ann > -1 & abs(bid.ask.pnl.ann) < .5 & abs(index.pnl.ann) < .5 ]
  qq1 <- felm(pnl.ann         ~ iBuyer.buyer ,data=toReg)
  qq2 <- felm(index.pnl.ann   ~ iBuyer.buyer ,data=toReg)
  qq3 <- felm(bid.ask.pnl.ann ~ iBuyer.buyer ,data=toReg)
  
  
  qq4 <- felm(pnl.ann         ~ iBuyer.buyer + log(living_sqft) + house.age| paste(zip5,qtr) +  age.bin + size.bin + multistory + airconditioning +quality + heating +garage + locationinfluence,data=toReg)
  qq5 <- felm(index.pnl.ann   ~ iBuyer.buyer + log(living_sqft) + house.age| paste(zip5,qtr) + age.bin + size.bin + multistory + airconditioning + quality + heating +garage + locationinfluence,data=toReg)
  qq6 <- felm(bid.ask.pnl.ann ~ iBuyer.buyer + log(living_sqft) + house.age| paste(zip5,qtr) +  age.bin + size.bin + multistory + airconditioning +quality + heating +garage +locationinfluence,data=toReg)
  
  stargazer(qq1,qq4,qq2,qq5,qq3,qq6,type='html',no.space=T,out = '../out/tables/A6B.html',
            dep.var.labels = c('Gross return (ann)','Index return (ann)','Non-index return (ann)'),
            keep = c('iBuyer.buyer'),
            omit.stat = c('adj.rsq','f','ser'),
            add.lines = list(c('House controls','N','Y','N','Y','N','Y'),
                             c('Zip x quarter FE','N','Y','N','Y','N','Y')))
  
}




Table_A6_Panel_A <- function() {
  
  data <- loadData() 
  data[,zip3 := substr(zip5,1,3)]
  
  # Median prices
  prices <- data[saleamount > quantile(data$saleamount,.01,na.rm=T) & saleamount < quantile(data$saleamount,.99,na.rm=T),j=list(index.price = median(saleamount,na.rm=T)),by=c('zip3','qtr')]
  
  # Get completed transactions
  data <- data[order(pclidirisfrmtd,qtr)]
  data[,buy.price  := saleamount]
  data[,sale.price := shift(saleamount,1,type='lead'),by='pclidirisfrmtd']
  data[,sale.qtr   := shift(qtr ,1,type='lead'),by='pclidirisfrmtd']
  data[,sale.date  := shift(date,1,type='lead'),by='pclidirisfrmtd']
  
  
  completed <- data[!is.na(sale.price) & sale.date != date]
  completed[,T := as.integer(sale.date- date)/365]
  completed[,pnl := sale.price / buy.price - 1]
  completed <- completed[pnl != 0] # get rid  of zero pnl observations
  completed <- merge(completed,prices,by=c('zip3','qtr'))
  completed <- merge(completed,prices,by.x=c('zip3','sale.qtr'),by.y=c('zip3','qtr'),suffixes=c('.buy','.sale'))
  completed[,index.pnl := index.price.sale / index.price.buy - 1]
  completed[,bid.ask.pnl := pnl - index.pnl]
  completed[,pnl.ann         := (pnl + 1)^(1/T) - 1]
  completed[,index.pnl.ann   := (index.pnl + 1)^(1/T) - 1]
  completed[,bid.ask.pnl.ann := (bid.ask.pnl + 1)^(1/T) - 1]
  
  
  completed[,Type := 'Individual']
  completed[iBuyer.buyer == 1,Type := 'iBuyer']
  completed[iBuyer.buyer == 0 & corporateindicator == 'Y',Type := 'Other corporate']
  
  completed <- completed[year %in% 2013:2018]
  
  # Apply some filters
  toReg <- completed[Type != 'Other corporate' & abs(pnl / (sale.qtr - qtr + 1)) < 1 & abs(pnl.ann) < 1 & abs(bid.ask.pnl.ann) < 1 & abs(index.pnl.ann) < 1]
  # toReg <- ready[Type != 'Other corporate' & pnl.ann < 1 & pnl.ann > -1 & abs(bid.ask.pnl.ann) < .5 & abs(index.pnl.ann) < .5 ]
  
  # We'll think of pnl by buyer year
  toReg[,owner := paste(owner1lastname,owner1firstnamemi)]
  
  # Do the grouping.
  iBuyers <- data.table(iBuyer = c('Opendoor','Offerpad','Zillow'),
                        match.strings = c('opendoor|open door|\\<od [a-z].*','offerpad|offer pad','zillow'))
  
  toReg[,iBuyer.name := '']
  
  for(ii in 1:nrow(iBuyers)) {
    name <- iBuyers$iBuyer[ii]
    toReg[grepl(owner,pattern=iBuyers$match.strings[ii],ignore.case=T),iBuyer.name := name]
  }
  
  toReg[iBuyer.name != '', owner := iBuyer.name]
  toReg <- toReg[owner != '']
  
  # Calculate portfolio return by year.
  qq <- toReg[T > 0 & owner != '',j=list(.N,T = mean(T,na.rm=TRUE),pnl = mean(pnl,na.rm=TRUE),bid.ask.pnl = mean(bid.ask.pnl,na.rm=TRUE),index.pnl = mean(index.pnl,na.rm=TRUE),
                                         pnl.ann = mean(pnl.ann,na.rm=TRUE),bid.ask.pnl.ann = mean(bid.ask.pnl.ann,na.rm=TRUE),index.pnl.ann = mean(index.pnl.ann,na.rm=TRUE)),by=c('qtr','owner','iBuyer.name')]
  
  qq[,Type := 'iBuyer']
  qq[iBuyer.name == '', Type := 'Individual']
  qq <- qq[T < 2 | Type != 'iBuyer'] # Too early for iBuyer
  
  
  qq1 <- qq[,j=list(N = mean(N),NS = sd(N),T = mean(T,na.rm=TRUE),TS = sd(T,na.rm=TRUE),pnl.prl.ann   = mean(pnl.ann,na.rm=TRUE),pnl.prl.ann.s   = sd(pnl.ann,na.rm=TRUE)),by='Type']
  

  # Calculate individualized statistics
  by.individual.asset <- toReg[,j=list(pnl.gross = mean(pnl,na.rm=TRUE),pnl.gross.sd = sd(pnl,na.rm=TRUE),
                                       pnl.gross.ann = mean(pnl.ann,na.rm=TRUE),pnl.gross.ann.sd = sd(pnl.ann,na.rm=TRUE)),by='Type']
  
  to.print <- data.table(Row = c('Individual mean','Individual SD','iBuyer mean','iBuyer SD'),
                         Holding_Period = c(qq1[Type == 'Individual']$T,qq1[Type == 'Individual']$TS,qq1[Type == 'iBuyer']$T,qq1[Type == 'iBuyer']$TS),
                         Gross_Return_Raw = c(by.individual.asset[Type=='Individual']$pnl.gross,by.individual.asset[Type=='Individual']$pnl.gross.sd,by.individual.asset[Type=='iBuyer']$pnl.gross,by.individual.asset[Type=='iBuyer']$pnl.gross.sd),
                         Gross_Return_Ann = c(by.individual.asset[Type=='Individual']$pnl.gross.ann,by.individual.asset[Type=='Individual']$pnl.gross.ann.sd,by.individual.asset[Type=='iBuyer']$pnl.gross.ann,by.individual.asset[Type=='iBuyer']$pnl.gross.ann.sd),
                         Prl_Size         = c(qq1[Type == 'Individual']$N,qq1[Type == 'Individual']$NS,qq1[Type == 'iBuyer']$N,qq1[Type == 'iBuyer']$NS),
                         Prl_Return       = c(qq1[Type == 'Individual']$pnl.prl.ann,qq1[Type == 'Individual']$pnl.prl.ann.s,qq1[Type == 'iBuyer']$pnl.prl.ann,qq1[Type == 'iBuyer']$pnl.prl.ann.s))
  
  stargazer(to.print,summary=F,type='html',out = '../out/tables/A6A.html')
    
}


Table_A6_Panel_A()
Table_A6_Panel_B()
